<html>
 <head>
  <meta charset="UTF-8">
 </head>
 <body>
  <h1 data-lake-id="kYiCx" id="kYiCx"><span data-lake-id="u41b363d9" id="u41b363d9">典型回答</span></h1>
  <p data-lake-id="u3d72a0fb" id="u3d72a0fb"><br></p>
  <p data-lake-id="uff0bb4e8" id="uff0bb4e8"><span data-lake-id="u9141f5c7" id="u9141f5c7">在SQL中，</span><code data-lake-id="u86ecd8d5" id="u86ecd8d5"><span data-lake-id="udf5ac1eb" id="udf5ac1eb">ON</span></code><span data-lake-id="u868a3808" id="u868a3808">和</span><code data-lake-id="u87293ae4" id="u87293ae4"><span data-lake-id="u6dd33266" id="u6dd33266">WHERE</span></code><span data-lake-id="u71ad91ae" id="u71ad91ae">子句都用于指定条件，但它们在JOIN操作中的应用和影响是不同的。</span></p>
  <p data-lake-id="u5abc301a" id="u5abc301a"><br></p>
  <p data-lake-id="ub50e9dc8" id="ub50e9dc8"><code data-lake-id="u45a2ddfa" id="u45a2ddfa"><span data-lake-id="ub7a14302" id="ub7a14302">ON</span></code><span data-lake-id="u7cc77cc0" id="u7cc77cc0">子句主要用在JOIN操作中，用于指定JOIN的条件。</span><strong><span data-lake-id="u4f755559" id="u4f755559">他仅影响JOIN操作的结果</span></strong><span data-lake-id="ua0d7b5c0" id="ua0d7b5c0">。</span></p>
  <p data-lake-id="uaa5cef2e" id="uaa5cef2e"><br></p>
  <pre lang="java"><code>
SELECT * FROM table1
JOIN table2
ON table1.id = table2.foreign_id;
</code></pre>
  <p data-lake-id="u5bee8aaa" id="u5bee8aaa"><br></p>
  <p data-lake-id="ubb6af255" id="ubb6af255"><code data-lake-id="ue3aeed83" id="ue3aeed83"><span data-lake-id="ubcb738aa" id="ubcb738aa">WHERE</span></code><span data-lake-id="u19b3e361" id="u19b3e361">子句用于对结果集进行过滤，无论是简单的SELECT查询还是复杂的JOIN查询。</span><code data-lake-id="u20b048ee" id="u20b048ee"><strong><span data-lake-id="uc6947f41" id="uc6947f41">WHERE</span></strong></code><strong><span data-lake-id="ucd3e02c4" id="ucd3e02c4">子句在JOIN操作之后应用，即在所有的JOIN操作完成后，对这个已经组合起来的数据集进行过滤。</span></strong></p>
  <pre lang="java"><code>
SELECT * FROM table1
JOIN table2
ON table1.id = table2.foreign_id
WHERE table1.column &gt; 100;
</code></pre>
  <p data-lake-id="u3c22edb5" id="u3c22edb5"><br></p>
  <h3 data-lake-id="4fb95da6" id="4fb95da6"><span data-lake-id="uf256eccd" id="uf256eccd">二者区别</span></h3>
  <p data-lake-id="ua01d3e9b" id="ua01d3e9b"><br></p>
  <ol list="ubc61d515">
   <li fid="u63f6b0db" data-lake-id="u8be71593" id="u8be71593"><strong><span data-lake-id="ueb4582f3" id="ueb4582f3">应用阶段</span></strong><span data-lake-id="u7b00d87f" id="u7b00d87f">：</span><code data-lake-id="ua32c4185" id="ua32c4185"><span data-lake-id="u0505f6d4" id="u0505f6d4">ON</span></code><span data-lake-id="u2d854f6c" id="u2d854f6c">子句在JOIN阶段应用，而</span><code data-lake-id="ubacab4e8" id="ubacab4e8"><span data-lake-id="uf5f72b3f" id="uf5f72b3f">WHERE</span></code><span data-lake-id="u001a3a6c" id="u001a3a6c">子句在所有JOIN操作完成后应用。</span></li>
   <li fid="u63f6b0db" data-lake-id="u2a8b6dc3" id="u2a8b6dc3"><strong><span data-lake-id="u062adf32" id="u062adf32">用途</span></strong><span data-lake-id="u02415df7" id="u02415df7">：</span><code data-lake-id="ucb64ce19" id="ucb64ce19"><span data-lake-id="u4dc7841e" id="u4dc7841e">ON</span></code><span data-lake-id="ua8af6086" id="ua8af6086">定义了如何JOIN两个表，</span><code data-lake-id="u10c99a88" id="u10c99a88"><span data-lake-id="u4956aac0" id="u4956aac0">WHERE</span></code><span data-lake-id="ue01674f9" id="ue01674f9">定义了如何筛选结果。</span></li>
  </ol>
  <p data-lake-id="u80fbae8c" id="u80fbae8c"><br></p>
  <h3 data-lake-id="ea0be9f2" id="ea0be9f2"><span data-lake-id="u9907c721" id="u9907c721">举例说明</span></h3>
  <p data-lake-id="u12934981" id="u12934981"><br></p>
  <p data-lake-id="ua50df0c5" id="ua50df0c5"><span data-lake-id="ue0faf978" id="ue0faf978">考虑以下两个查询：</span></p>
  <p data-lake-id="u36addea3" id="u36addea3"><br></p>
  <ul list="u748fabe4">
   <li fid="ua70303c9" data-lake-id="u5a07893e" id="u5a07893e"><span data-lake-id="u31b37dd6" id="u31b37dd6"> 使用</span><code data-lake-id="u7ff6a1d6" id="u7ff6a1d6"><span data-lake-id="ue1867998" id="ue1867998">ON</span></code><span data-lake-id="u566eaffc" id="u566eaffc">子句： </span></li>
  </ul>
  <pre lang="java"><code>
SELECT * FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
   AND departments.name = 'IT';
</code></pre>
  <p data-lake-id="uf02f62d4" id="uf02f62d4"><span data-lake-id="u2fb72a64" id="u2fb72a64"><br></span><span data-lake-id="u8bf044d0" id="u8bf044d0">这将返回所有员工，即使他们不在IT部门。对于不在IT部门的员工，部门相关的列将为NULL。 </span></p>
  <p data-lake-id="u8883edbe" id="u8883edbe"><span data-lake-id="ua91bd6e8" id="ua91bd6e8">​</span><br></p>
  <ul list="u71c7899c">
   <li fid="ube6d3b4a" data-lake-id="u4281244a" id="u4281244a"><span data-lake-id="ufd7c3b73" id="ufd7c3b73"> 使用</span><code data-lake-id="u9f7dab4b" id="u9f7dab4b"><span data-lake-id="ub5a63359" id="ub5a63359">WHERE</span></code><span data-lake-id="uf5bd8d27" id="uf5bd8d27">子句： </span></li>
  </ul>
  <pre lang="java"><code>
SELECT * FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
WHERE departments.name = 'IT';
</code></pre>
  <p data-lake-id="u0eedfdeb" id="u0eedfdeb"><span data-lake-id="ua5ebe760" id="ua5ebe760"><br></span><span data-lake-id="u2316b41f" id="u2316b41f">这将仅返回IT部门的员工。那些不在IT部门的员工将被过滤掉。 </span></p>
  <p data-lake-id="ub0129fb3" id="ub0129fb3"><br></p>
  <p data-lake-id="u0fd7a1ea" id="u0fd7a1ea"><span data-lake-id="u000a0370" id="u000a0370">通过这个例子可以看出，尽管这两个查询看起来相似，但它们的结果会非常不同，这是由</span><code data-lake-id="ub33c7e8b" id="ub33c7e8b"><span data-lake-id="ub3309ef7" id="ub3309ef7">ON</span></code><span data-lake-id="u10211d44" id="u10211d44">和</span><code data-lake-id="u9d471945" id="u9d471945"><span data-lake-id="u3ca9bc3d" id="u3ca9bc3d">WHERE</span></code><span data-lake-id="u19f27a3b" id="u19f27a3b">子句的不同用法造成的。</span></p>
 </body>
</html>